﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Data.SqlClient;
using System.IO;
using System.Text.RegularExpressions;
using System.Collections;
using System.Diagnostics;


namespace bLogical.BizTalkManagement.Helpers
{
    public class DatabaseManager
    {
        #region Events
        public delegate void PercentComplete(object sender, PercentEventArgs e);
        public event PercentComplete OnPercentComplete;

        public delegate void Complete(object sender, CompleteEventArgs e);
        public event Complete OnComplete;
        #endregion
       
        #region public methods
        /// <summary>
        /// 
        /// </summary>
        /// <param name="database"></param>
        /// <param name="backupPath"></param>
        /// <param name="mark"></param>
        public bool Restore(String database, String backupPath, string mark)
        {
            bool processFlag = false;
            try
            {
                List<BackUpFile> backUpFiles = new List<BackUpFile>();

                if (String.IsNullOrEmpty(mark))
                    mark = GetMarks(backupPath).Last();

                foreach (string fileName in Directory.GetFiles(backupPath, "*" + database + "*.bak"))
                {
                    backUpFiles.Add(new BackUpFile(database, fileName, "BTS"));
                }

                if (backUpFiles.Count() > 0)
                {
                    var dataFiles = from b in backUpFiles
                                    where b.Database == database && b.IsFull
                                    orderby b.Date descending
                                    select b;

                    BackUpFile dataFile = dataFiles.First(d => d.Date <= MarkToDate(mark));

                    var logFiles = (from l in backUpFiles
                                    where !l.IsFull &&
                                            l.Database == database &&
                                            l.Date >= dataFile.Date &&
                                            l.Date <= MarkToDate(mark)
                                    orderby l.Database, l.Date
                                    select l);

                    logFiles.Last().IsLastLog = true;
                    RestoreInternal(dataFile, logFiles, backupPath);
                    processFlag = true;
                }
                else
                {
                    RaiseMessage(string.Format("The backup set for Database::[{0}] does not exist.", database));
                    processFlag = false;
                }

                return processFlag;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="backupPath"></param>
        /// <returns></returns>
        public IEnumerable<String> GetMarks(String backupPath)
        {
            try
            {
                List<string> markList = new List<string>();

                foreach (string fileName in Directory.GetFiles(backupPath, "*Log*.bak"))
                {
                    markList.Add(BackUpFile.GetMark(fileName, "BTS"));
                }

                var marks = (from m in markList
                                 orderby m
                                 select m
                                ).Distinct();
                return marks;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
        #region Private methods
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataFile"></param>
        /// <param name="logFiles"></param>
        /// <param name="backupPath"></param>
        private void RestoreInternal(BackUpFile dataFile, IEnumerable<BackUpFile> logFiles, String backupPath)
        {
            try
            {
                RaiseMessage(String.Format("\nDatabase: {0} on {1}", dataFile.Database, dataFile.Server));
                String connectionString = new SqlConnectionStringBuilder()
                {
                    DataSource = dataFile.Server,
                    InitialCatalog = "master",
                    IntegratedSecurity = true,
                    ConnectTimeout = 30
                }.ConnectionString;

                SqlConnection sqlConnection = new SqlConnection(connectionString);
                ServerConnection connection = new ServerConnection(sqlConnection);
                Server sqlServer = new Server(connection);
            
                connection.StatementTimeout = 60;
                sqlConnection.Open();

                Database db = sqlServer.Databases[dataFile.Database];

                if (db != null)
                    SetUserMode(sqlConnection, dataFile.Database, true);
                else
                    RaiseMessage("Unable to connect to database. Trying to restore without setting the database in Single User Mode.");

                RaiseMessage(string.Format("Restoring data file with NORECOVERY [{1}].", dataFile.Database, Path.GetFileName(dataFile.FileName)));
                RestoreDatabaseFile(
                    sqlServer,
                    dataFile,
                    Path.Combine(backupPath, dataFile.FileName));

                foreach (var logFile in logFiles)
                {
                    RaiseMessage(string.Format("Restoring log file with {1} [{2}]", dataFile.Database, logFile.IsLastLog ? "MARK" : "NORECOVERY", Path.GetFileName(logFile.FileName)));
                    RestoreLogFile(
                    sqlServer,
                    logFile,
                    Path.Combine(backupPath, logFile.FileName));
                }
                
                if (db != null)
                {
                    db.SetOnline();
                    SetUserMode(sqlConnection, dataFile.Database, false);
                }
                
                sqlServer.Refresh();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlServer"></param>
        /// <param name="backUpFile"></param>
        /// <param name="filePath"></param>
        private void RestoreDatabaseFile(
            Server sqlServer,
            BackUpFile backUpFile,
            String filePath)
        {
            try
            {
                Restore sqlRestore = new Restore();
                sqlRestore.Action = RestoreActionType.Database;
                sqlRestore.Devices.Add(new BackupDeviceItem(filePath, DeviceType.File));
                sqlRestore.Database = backUpFile.Database;
                sqlRestore.ReplaceDatabase = true;
                sqlRestore.NoRecovery = true;

                sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
                sqlRestore.PercentCompleteNotification = 10;

                sqlRestore.PercentComplete += new PercentCompleteEventHandler(sqlRestore_PercentComplete);

                sqlRestore.SqlRestore(sqlServer);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlServer"></param>
        /// <param name="backUpFile"></param>
        /// <param name="filePath"></param>
        private void RestoreLogFile(
            Server sqlServer,
            BackUpFile backUpFile,
            String filePath)

        {
            try
            {
                Restore sqlRestore = new Restore();
                sqlRestore.Action = RestoreActionType.Log;
                sqlRestore.Devices.Add(new BackupDeviceItem(filePath, DeviceType.File));
                sqlRestore.Database = backUpFile.Database;
                sqlRestore.ReplaceDatabase = true;

                sqlRestore.NoRecovery = backUpFile.IsLastLog ? false : true;
                sqlRestore.StopAtMarkName = backUpFile.IsLastLog ? backUpFile.MarkName : null;

                sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
                sqlRestore.PercentCompleteNotification = 10;

                sqlRestore.PercentComplete += new PercentCompleteEventHandler(sqlRestore_PercentComplete);

                sqlRestore.SqlRestore(sqlServer);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="mark"></param>
        /// <returns></returns>
        private DateTime MarkToDate(string mark)
        {
            string[] tokens = Path.GetFileNameWithoutExtension(mark).Split('_');
            return new DateTime(int.Parse(tokens[1]),
                int.Parse(tokens[2]),
                int.Parse(tokens[3]),
                int.Parse(tokens[4]),
                int.Parse(tokens[5]),
                int.Parse(tokens[6]),
                int.Parse(tokens[7]));
        }
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="database"></param>
        /// <param name="singleUser"></param>
        private void SetUserMode(SqlConnection connection, string database, bool singleUser)
        {
            String singleUserCmd = "alter database {0} set SINGLE_USER";
            String multiUserCmd = "alter database {0} set MULTI_USER";
            string command;
            if (singleUser)
            {
                command = String.Format(singleUserCmd, database);
            }
            else
            {
                command = String.Format(multiUserCmd, database);
            }

            SqlCommand cmd = new SqlCommand(command, connection);
            
            cmd.ExecuteNonQuery();
            RaiseMessage(String.Format("Database {0} has been set in {1} mode", database, singleUser ? "SINGLE_USER" : "MULTI_USER"));
            
            return;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void sqlRestore_PercentComplete(object sender, PercentCompleteEventArgs e)
        {
            if (OnPercentComplete != null)
            {
                OnPercentComplete(null, new PercentEventArgs() { PercentComplete = e.Percent });
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void sqlRestore_Complete(object sender, ServerMessageEventArgs e)
        {
            if (OnComplete != null)
            {
                OnComplete(null, new CompleteEventArgs() { Message = e.Error.Message });
            }
        }
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="msg"></param>
        private void RaiseMessage(string msg)
        {
            if (OnComplete != null)
            {
                OnComplete(null, new CompleteEventArgs() { Message = msg });
            }
        }
        #endregion
        
        #region Private members
        
        #endregion
    }
    

    public class CompleteEventArgs : EventArgs 
    {
        public String Message { get; set; }
    }
    

    public class PercentEventArgs : EventArgs
    {
        public int PercentComplete { get; set; }
    }

}
